package dao.upload;

import entity.upload.EntityAccessory;
import util.DBUtil;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 附件上传DAO
 *
 * @author xusucheng
 * @create 2017-12-29
 **/
public class AccessoryDao {
    public static void add(EntityAccessory entity) {
        Connection conn = DBUtil.getConnection();
        String sql = "insert into tbl_accessory(file_name,file_size,file_ext_name,file_path) values(?,?,?,?)";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, entity.getFileName());
            ps.setDouble(2, entity.getFileSize());
            ps.setString(3, entity.getFile_ext_name());
            ps.setString(4, entity.getFilePath());
            ps.execute();
            //conn.commit();

            DBUtil.close(null, ps, conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static List<EntityAccessory> list() {
        Connection conn = DBUtil.getConnection();
        String sql = "select id,file_name,file_size,file_ext_name,file_path from tbl_accessory";
        List<EntityAccessory> accessoryList = new ArrayList<>();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                EntityAccessory entity = new EntityAccessory();
                entity.setId(rs.getInt("id"));
                entity.setFileName(rs.getString("file_name"));
                entity.setFileSize(new BigDecimal(rs.getDouble("file_size") / 1024).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
                entity.setFile_ext_name(rs.getString("file_ext_name"));
                entity.setFilePath(rs.getString("file_path"));
                accessoryList.add(entity);
            }

            DBUtil.close(rs, ps, conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return accessoryList;

    }

    public static EntityAccessory load(int id){
        Connection conn = DBUtil.getConnection();
        PreparedStatement ps=null;
        ResultSet rs=null;
        EntityAccessory entity = new EntityAccessory();
        String sql = "select id, file_name,file_size,file_ext_name,file_path from tbl_accessory where id=?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                entity.setId(rs.getInt("id"));
                entity.setFileName(rs.getString("file_name"));
                entity.setFileSize(rs.getDouble("file_size"));
                entity.setFile_ext_name(rs.getString("file_ext_name"));
                entity.setFilePath(rs.getString("file_path"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,conn);
        }

        return entity;
    }

    public static void remove(int id) {
        Connection conn = DBUtil.getConnection();
        String sql = "delete from tbl_accessory where id=?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.execute();
            //conn.commit(); mysql默认开启了autocommit

            DBUtil.close(null,ps,conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
